package com.che.software.testato.domain.dao.jdbc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.che.software.testato.domain.dao.ITestCaseDAO;
import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO;
import com.che.software.testato.domain.dao.jdbc.exception.TestCaseSearchDAOException;
import com.che.software.testato.domain.entity.TestCase;

/**
 * JDBC implementation of the DAO interface dedicated to the test cases
 * management.
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @copyright Che Software.
 * @license GNU General Public License.
 * @see AbstractDAO, ITestCaseDAO.
 * @since July, 2011.
 * 
 *        This file is part of Testato.
 * 
 *        Testato is free software: you can redistribute it and/or modify it
 *        under the terms of the GNU General Public License as published by the
 *        Free Software Foundation, either version 3 of the License, or (at your
 *        option) any later version.
 * 
 *        Testato is distributed in the hope that it will be useful, but WITHOUT
 *        ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 *        FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
 *        for more details.
 * 
 *        You should have received a copy of the GNU General Public License
 *        along with Testato. If not, see <http://www.gnu.org/licenses/>.
 * 
 *        Testato's logo is a creation of Arrioch
 *        (http://arrioch.deviantart.com/) and it's distributed under the terms
 *        of the Creative Commons License.
 */
@Repository("testCaseDAO")
public class TestCaseDAO extends AbstractDAO implements ITestCaseDAO {

	/**
	 * Constants.
	 */
	private static final Logger LOGGER = Logger.getLogger(TestCaseDAO.class);

	/**
	 * Test cases variants to generate search.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @return the resulting object list.
	 * @since July, 2011.
	 * @throws TestCaseSearchDAOException if an error occurs during the search.
	 */
	@Override
	public List<TestCase> searchTestCasesVariantsToGenerate() throws TestCaseSearchDAOException {
		LOGGER.debug("searchTestCasesVariantsToGenerate().");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return getQueryRunner().query(connection, "SELECT test_case_id AS testCaseId, item.label AS label, ve.version_id AS versionId, project.project_id AS projectId, ve.name AS versionName, project.name AS projectName FROM test_case JOIN item USING(test_case_id) LEFT JOIN action_inclusive_item aii ON(aii.item_id = item.item_id) LEFT JOIN action_exclusive_item aei ON(aei.item_id = item.item_id) JOIN action a ON(a.action_id = COALESCE(aei.action_id, aii.action_id)) JOIN hierarchy_action_plan h ON(h.action_plan_id = a.action_plan_id) JOIN hierarchy_version v ON(v.hierarchy_id = h.hierarchy_id) JOIN version ve ON(ve.version_id = v.version_id) JOIN project ON(project.project_id = ve.project_id) WHERE NOT EXISTS( SELECT variant_id FROM variant va WHERE va.test_case_id = test_case_id ) ",
					new BeanListHandler<TestCase>(TestCase.class));
		} catch (SQLException e) {
			throw new TestCaseSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}
}